﻿using System;
using System.Data;
using PCSComMaterials.Inventory.BO;
using PCSComMaterials.Inventory.DS;
using PCSComProduct.Items.DS;
using PCSComProduction.DCP.DS;
using PCSComProduction.WorkOrder.DS;
using PCSComUtils.Common;
using PCSComUtils.Common.BO;
using PCSComUtils.MasterSetup.DS;
using PCSComUtils.PCSExc;
using PCSComUtils.DataContext;
using System.Transactions;
using PCSComUtils.DataAccess;
using System.Linq;

namespace PCSComProduction.WorkOrder.BO
{
    public class WorkOrderCompletionBO 
    {
        private const string THIS = "PCSComProduction.WorkOrder.BO.WorkOrderCompletionBO";
        private string[] strComment = new string[1];

        private const string LineField = "Line";
        private const string WorkOrderNoField = "WorkOrderNo";
        private const string WorkOrderLineField = "WorkOrderLine";
        private const string ParentPartNumberField = "ParentPartNumber";
        private const string ParentPartModelField = "ParentPartModel";
        private const string ParentPartNameField = "ParentPartName";
        private const string ParentStockUmField = "ParentStockUM";
        private const string PartNumberField = "PartNumber";
        private const string PartNameField = "PartName";
        private const string PartModelField = "PartModel";
        private const string ChildStockUmField = "ChildStockUM";
        private const string NeedQuantityField = "NeedQuantity";
        private const string AvailableQuantityField = "AvailableQuantity";
        private const string ShortageQuantityField = "ShortageQuantity";

        public bool InsertWorkOrderCompletion(PRO_WorkOrderCompletion objWOCompletion, int iProductionLineID, decimal decQty, DateTime dtmSeverDate, DataTable dtbListOfComponentShortage, DataTable dtbCheckSubtract)
        {
            string METHOD_NAME = "InsertWorkOrderCompletion()";

            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    try
                    {
                        #region Insert WorkOrderCompletion
                        db.PRO_WorkOrderCompletions.InsertOnSubmit(objWOCompletion);
                        db.SubmitChanges();
                        #endregion

                        #region DO NOT ALLOW USER COMPLETE THE WORK ORDER THAT NOT HAS COMPONENT(S)

                        if (dtbListOfComponentShortage.Rows.Count == 0)
                            throw new PCSBOException(ErrorCode.MESSAGE_NOT_HAS_COMPONENT_TO_COMPLETE, METHOD_NAME, new Exception());

                        #endregion

                        // Set Comment status
                        switch (objWOCompletion.QAStatus)
                        {
                            case (int)QAStatusEnum.Passed:
                                strComment[0] = QAStatusEnum.Passed.ToString();
                                break;
                            case (int)QAStatusEnum.Failure:
                                strComment[0] = QAStatusEnum.Failure.ToString();
                                break;
                            case (int)QAStatusEnum.Unknow:
                                strComment[0] = QAStatusEnum.Unknow.ToString();
                                break;
                        }

                        //Subtract quantity in buffer location
                        int intProductType = 0;
                        for (int i = 0; i < dtbListOfComponentShortage.Rows.Count; i++)
                        {
                            #region preparation

                            try
                            {
                                intProductType = Convert.ToInt32(dtbListOfComponentShortage.Rows[i][ITM_ProductTable.PRODUCTTYPEID_FLD]);
                            }
                            catch
                            {
                            }
                            if (intProductType == (int) EnumProductType.SubMaterial || intProductType == (int) EnumProductType.SubMaterialProduce)
                            {
                                continue;
                            }
                            Decimal decRate = Decimal.One;
                            try
                            {
                                decRate = (decimal) dtbListOfComponentShortage.Rows[i][ITM_ProductTable.RATE_FLD];
                            }
                            catch
                            {
                            }

                            var decBomQty = (decimal) dtbListOfComponentShortage.Rows[i][PRO_WorkOrderBomDetailTable.REQUIREDQUANTITY_FLD];

                            string strRevision = dtbListOfComponentShortage.Rows[i][ITM_ProductTable.REVISION_FLD].ToString();
                            decimal decShortgeQuantity;
                            if (strRevision == "CTTC")
                            {
                                decShortgeQuantity = objWOCompletion.CompletedQuantity*decBomQty;
                            }
                            else
                            {
                                decShortgeQuantity = objWOCompletion.CompletedQuantity*decBomQty*decRate;
                            }

                            DataRow[] drowItemsInCache = dtbCheckSubtract.Select(ITM_ProductTable.PRODUCTID_FLD + "=" + (int) dtbListOfComponentShortage.Rows[i][PRO_WorkOrderBomDetailTable.COMPONENTID_FLD]);
                            string strProductCode = dtbListOfComponentShortage.Rows[i][ITM_ProductTable.CODE_FLD].ToString();
                            if (drowItemsInCache.Length == 0)
                            {
                                throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, strProductCode, new PCSException());
                            }
                            decimal decBinAvailable = (decimal) drowItemsInCache[0]["BinAvailable"];
                            if (decBinAvailable < decShortgeQuantity && (decShortgeQuantity - decBinAvailable) >= 1)
                            {
                                throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, strProductCode, new Exception());
                            }

                            #endregion

                            #region History of component
                            var objTransactionHistory = new MST_TransactionHistory();

                            if (drowItemsInCache[0][MST_BINTable.BINID_FLD] != null && drowItemsInCache[0][MST_BINTable.BINID_FLD].ToString() != string.Empty)
                                objTransactionHistory.BinID = Convert.ToInt32(drowItemsInCache[0][MST_BINTable.BINID_FLD]);
                            objTransactionHistory.TransDate = dtmSeverDate;
                            objTransactionHistory.TranTypeID = 19;
                            if (drowItemsInCache[0][ITM_ProductTable.LOCATIONID_FLD] != null && drowItemsInCache[0][ITM_ProductTable.LOCATIONID_FLD].ToString() != string.Empty)
                                objTransactionHistory.LocationID = Convert.ToInt32(drowItemsInCache[0][MST_BINTable.LOCATIONID_FLD]);
                            if (drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD] != null && drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD].ToString() != string.Empty)
                                objTransactionHistory.ProductID = Convert.ToInt32(drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD]);
                            objTransactionHistory.CCNID = objWOCompletion.CCNID;
                            objTransactionHistory.Lot = objWOCompletion.Lot;
                            objTransactionHistory.MasterLocationID = objWOCompletion.MasterLocationID;
                            if (dtbListOfComponentShortage.Rows[i][ITM_ProductTable.STOCKUMID_FLD] != null && dtbListOfComponentShortage.Rows[i][ITM_ProductTable.STOCKUMID_FLD].ToString() != string.Empty)
                                objTransactionHistory.StockUMID = Convert.ToInt32(dtbListOfComponentShortage.Rows[i][ITM_ProductTable.STOCKUMID_FLD]);
                            objTransactionHistory.Serial = objWOCompletion.Serial;
                            objTransactionHistory.PostDate = objWOCompletion.PostDate;
                            objTransactionHistory.RefMasterID = objWOCompletion.WorkOrderCompletionID;
                            objTransactionHistory.Quantity = -decShortgeQuantity;
                            objTransactionHistory.IssuePurposeID = (int)PurposeEnum.Completion;
                            db.MST_TransactionHistories.InsertOnSubmit(objTransactionHistory);
                            #endregion

                            #region Cache

                            int intLocationID = (int)drowItemsInCache[0][MST_BINTable.LOCATIONID_FLD];
                            int intBinID = (int)drowItemsInCache[0][MST_BINTable.BINID_FLD];
                            int intProductID = (int)drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD];

                            // bin cache
                            var objBin = db.IV_BinCaches.SingleOrDefault(e => e.LocationID == intLocationID && e.BinID == intBinID && e.ProductID == intProductID);
                            if (objBin != null)
                            {
                                if (objBin.OHQuantity != null)
                                {
                                    if (objBin.OHQuantity - decShortgeQuantity < 0)
                                    {
                                        objBin.OHQuantity = 0;
                                    }
                                    else {
                                        objBin.OHQuantity = objBin.OHQuantity - decShortgeQuantity;
                                    }
                                }
                            }
                            // location cache
                            var objLocation = db.IV_LocationCaches.SingleOrDefault(e => e.LocationID == intLocationID && e.ProductID == intProductID);
                            if (objLocation != null)
                            {
                                if (objLocation.OHQuantity != null)
                                {
                                    if (objLocation.OHQuantity - decShortgeQuantity < 0)
                                    {
                                        objLocation.OHQuantity = 0;
                                    }
                                    else
                                    {
                                        objLocation.OHQuantity = objLocation.OHQuantity - decShortgeQuantity;
                                    }
                                }
                            }

                            // master location cache
                            var objMasLocation = db.IV_MasLocCaches.SingleOrDefault(e => e.ProductID == intProductID);
                            if (objMasLocation != null)
                            {
                                if (objMasLocation.OHQuantity != null)
                                {
                                    if (objMasLocation.OHQuantity - decShortgeQuantity < 0)
                                    {
                                        objMasLocation.OHQuantity = 0;
                                    }
                                    else
                                    {
                                        objMasLocation.OHQuantity = objMasLocation.OHQuantity - decShortgeQuantity;
                                    }
                                }
                            }
                            db.SubmitChanges();
                            #endregion
                        }

                        #region Cache

                        if (objWOCompletion.QAStatus == (int)QAStatusEnum.Passed)
                        {
                            // bin cache
                            var objBin = db.IV_BinCaches.SingleOrDefault(e => e.LocationID == objWOCompletion.LocationID && e.BinID == objWOCompletion.BinID && e.ProductID == objWOCompletion.ProductID);
                            if (objBin != null)
                            {
                                objBin.OHQuantity = objBin.OHQuantity + objWOCompletion.CompletedQuantity;
                            }
                            else
                            {
                                IV_BinCache obj = new IV_BinCache();
                                obj.CCNID = objWOCompletion.CCNID;
                                obj.MasterLocationID = objWOCompletion.MasterLocationID;
                                obj.LocationID = objWOCompletion.LocationID;
                                if (objWOCompletion.BinID != null)
                                {
                                    int iBinID = objWOCompletion.BinID ?? 0;
                                    obj.BinID = iBinID;
                                }
                                obj.ProductID = objWOCompletion.ProductID;
                                obj.OHQuantity = objWOCompletion.CompletedQuantity;
                                db.IV_BinCaches.InsertOnSubmit(obj);
                            }


                            // location cache
                            var objLocation = db.IV_LocationCaches.SingleOrDefault(e => e.LocationID == objWOCompletion.LocationID && e.ProductID == objWOCompletion.ProductID);
                            if (objLocation != null)
                            {
                                objLocation.OHQuantity = objLocation.OHQuantity ?? 0;
                                objLocation.OHQuantity = objLocation.OHQuantity + objWOCompletion.CompletedQuantity;
                            }
                            else
                            {
                                // add new record
                                IV_LocationCache obj = new IV_LocationCache();
                                obj.CCNID = objWOCompletion.CCNID;
                                obj.MasterLocationID = objWOCompletion.MasterLocationID;
                                obj.LocationID = objWOCompletion.LocationID;
                                obj.ProductID = objWOCompletion.ProductID;
                                obj.OHQuantity = objWOCompletion.CompletedQuantity;
                                db.IV_LocationCaches.InsertOnSubmit(obj);
                            }
                            
                            // master location cache
                            var objMasLocation = db.IV_MasLocCaches.SingleOrDefault<IV_MasLocCache>(e => e.ProductID == objWOCompletion.ProductID && e.MasterLocationID==objWOCompletion.MasterLocationID);
                            if (objMasLocation != null)
                            {
                                objMasLocation.OHQuantity = objMasLocation.OHQuantity ?? 0;
                                objMasLocation.OHQuantity = objMasLocation.OHQuantity + objWOCompletion.CompletedQuantity;
                                objMasLocation.SupplyQuantity = objMasLocation.SummItemCost21 = 0;
                            }
                            else
                            {
                                // add new record
                                IV_MasLocCache obj = new IV_MasLocCache();
                                obj.CCNID = objWOCompletion.CCNID;
                                obj.MasterLocationID = objWOCompletion.MasterLocationID;
                                obj.ProductID = objWOCompletion.ProductID;
                                obj.OHQuantity = objWOCompletion.CompletedQuantity;
                                db.IV_MasLocCaches.InsertOnSubmit(obj);
                            }
                        }

                        #endregion

                        if (decQty <= objWOCompletion.CompletedQuantity)
                        {
                            var workOrderDetail = db.PRO_WorkOrderDetails.FirstOrDefault(e => e.WorkOrderDetailID == objWOCompletion.WorkOrderDetailID);
                            workOrderDetail.Status = (int)WOLineStatus.MfgClose;
                        }

                        //SaveTransactionHistory
                        MST_TransactionHistory objTransactionHis = new MST_TransactionHistory();
                        objTransactionHis.BinID = objWOCompletion.BinID;
                        objTransactionHis.TransDate = dtmSeverDate;
                        objTransactionHis.TranTypeID = 19;
                        objTransactionHis.LocationID = objWOCompletion.LocationID;
                        objTransactionHis.ProductID = objWOCompletion.ProductID;
                        objTransactionHis.CCNID = objWOCompletion.CCNID;
                        objTransactionHis.Lot = objWOCompletion.Lot;
                        objTransactionHis.MasterLocationID = objWOCompletion.MasterLocationID;
                        objTransactionHis.StockUMID = objWOCompletion.StockUMID;
                        objTransactionHis.Serial = objWOCompletion.Serial;
                        objTransactionHis.PostDate = objWOCompletion.PostDate;
                        objTransactionHis.RefMasterID = objWOCompletion.WorkOrderCompletionID;
                        objTransactionHis.Quantity = objWOCompletion.CompletedQuantity;
                        objTransactionHis.IssuePurposeID = (int)PurposeEnum.Completion;
                        db.MST_TransactionHistories.InsertOnSubmit(objTransactionHis);
                        db.SubmitChanges();
                    }
                    catch (PCSBOException ex)
                    {
                        if (ex.mCode == ErrorCode.SQLDUPLICATE_KEYCODE)
                            throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                        if (ex.mCode == ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE)
                            throw new PCSDBException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, ex.mMethod, ex);
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                    }
                }
                // complete transaction
                trans.Complete();
            }

            return true;
        }
        /// <summary>
        /// WorkOrderCompletion
        /// </summary>
        /// <param name="pobjWOCompletion"></param>
        /// <author>Trada</author>
        /// <date>Thursday, August 4 2005</date>
        public int WorkOrderCompletionForNewBussiness(object pobjWOCompletion, int pintProductionLineID, decimal pdecQuantity, DateTime dtmServerDate)
        {
            const string METHOD_NAME = THIS + ".WorkOrderCompletionForNewBussiness()";
            const string BINAVAILABLE_FLD = "BinAvailable";
            //Add 
            PRO_WorkOrderCompletionDS dsWOCompletion = new PRO_WorkOrderCompletionDS();
            MST_TransactionHistoryDS dsHistory = new MST_TransactionHistoryDS();
            PRO_ProductionLineDS dsProLine = new PRO_ProductionLineDS();
            IV_MasLocCacheDS dsMasLoc = new IV_MasLocCacheDS();
            IV_LocationCacheDS dsLoc = new IV_LocationCacheDS();
            IV_BinCacheDS dsBin = new IV_BinCacheDS();
            DataSet dstHistory = dsHistory.List(0);
            int pintWOCompletionID = dsWOCompletion.AddAndReturnID(pobjWOCompletion);

            PRO_WorkOrderCompletionVO voWOCompletion = (PRO_WorkOrderCompletionVO)pobjWOCompletion;

            //HACK: TUANDM 10-27-2005				
            int pintWODetailID = ((PRO_WorkOrderCompletionVO)pobjWOCompletion).WorkOrderDetailID;
            // list of component is not SubMaterial (Vat Lieu Phu) and SubMaterialProduce (Hang SX - VLP)
            DataTable dtbListOfComponentShortage = new PRO_WOBOMDS().ListComponentByWODetail(pintWODetailID);

            #region 07-08-2006 dungla: DO NOT ALLOW USER COMPLETE THE WORK ORDER THAT NOT HAS COMPONENT(S)

            if (dtbListOfComponentShortage.Rows.Count == 0)
                throw new PCSBOException(ErrorCode.MESSAGE_NOT_HAS_COMPONENT_TO_COMPLETE, METHOD_NAME, new Exception());

            #endregion

            // gets list of component
            string strProductIDs = voWOCompletion.ProductID.ToString();
            foreach (DataRow dr in dtbListOfComponentShortage.Rows)
                strProductIDs += "," + dr[PRO_WOBOMTable.COMPONENTID_FLD];

            DateTime dtmCurrentDate = (new UtilsBO()).GetDBDate().AddDays(1);
            DataTable dtbCheckSubtract = dsWOCompletion.GetAvailableQuantityByProductionLinePostDate(dtmCurrentDate, pintProductionLineID, voWOCompletion.WorkOrderDetailID);
            PRO_ProductionLineVO voProLine = (PRO_ProductionLineVO)dsProLine.GetObjectVO(pintProductionLineID);
            string strLocationID = voProLine.LocationID.ToString() + "," + voWOCompletion.LocationID.ToString();
            // get list of inventory
            DataSet dstMasLoc = dsMasLoc.ListAllCache(strProductIDs);
            DataSet dstLoc = dsLoc.ListAllCache(strLocationID, strProductIDs);
            DataSet dstBin = dsBin.ListAllBinCache(strLocationID, strProductIDs);

            // Set Comment status
            switch (voWOCompletion.QAStatus)
            {
                case (int)QAStatusEnum.Passed:
                    strComment[0] = QAStatusEnum.Passed.ToString();
                    break;
                case (int)QAStatusEnum.Failure:
                    strComment[0] = QAStatusEnum.Failure.ToString();
                    break;
                case (int)QAStatusEnum.Unknow:
                    strComment[0] = QAStatusEnum.Unknow.ToString();
                    break;
            }

            //Subtract quantity in buffer location
            decimal decShortgeQuantity;
            decimal decBinAvailable;
            int intProductType = 0;
            for (int i = 0; i < dtbListOfComponentShortage.Rows.Count; i++)
            {
                try
                {
                    intProductType = Convert.ToInt32(dtbListOfComponentShortage.Rows[i][ITM_ProductTable.PRODUCTTYPEID_FLD]);
                }
                catch { }
                if (intProductType == (int)EnumProductType.SubMaterial ||
                    intProductType == (int)EnumProductType.SubMaterialProduce)
                    continue;
                Decimal decRate = Decimal.One;
                try
                {
                    decRate = (decimal)dtbListOfComponentShortage.Rows[i][ITM_ProductTable.RATE_FLD];
                }
                catch { }

                decimal decBOMQty = (decimal)dtbListOfComponentShortage.Rows[i][PRO_WorkOrderBomDetailTable.REQUIREDQUANTITY_FLD];

                string strRevision = dtbListOfComponentShortage.Rows[i][ITM_ProductTable.REVISION_FLD].ToString();
                if (strRevision == "CTTC")
                    decShortgeQuantity = voWOCompletion.CompletedQuantity * decBOMQty;
                else
                    decShortgeQuantity = voWOCompletion.CompletedQuantity * decBOMQty * decRate;

                var productCode = dtbListOfComponentShortage.Rows[i][ITM_ProductTable.CODE_FLD].ToString();

                DataRow[] drowItemsInCache = dtbCheckSubtract.Select(ITM_ProductTable.PRODUCTID_FLD + "=" + (int)dtbListOfComponentShortage.Rows[i][PRO_WorkOrderBomDetailTable.COMPONENTID_FLD]);
                if (drowItemsInCache.Length == 0)
                {
                    throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, productCode, new Exception());
                }
                decBinAvailable = (decimal)drowItemsInCache[0][BINAVAILABLE_FLD];
                if (decBinAvailable < decShortgeQuantity)
                {
                    throw new PCSBOException(ErrorCode.MESSAGE_NOT_ENOUGH_COMPONENT_TO_COMPLETE, productCode, new Exception());
                }

                #region History of component

                DataRow drowHistory = dstHistory.Tables[0].NewRow();
                drowHistory[MST_TransactionHistoryTable.BINID_FLD] = drowItemsInCache[0][MST_BINTable.BINID_FLD];
                drowHistory[MST_TransactionHistoryTable.TRANSDATE_FLD] = dtmServerDate;
                drowHistory[MST_TransactionHistoryTable.TRANTYPEID_FLD] = 19;
                drowHistory[MST_TransactionHistoryTable.LOCATIONID_FLD] = drowItemsInCache[0][MST_BINTable.LOCATIONID_FLD];
                drowHistory[MST_TransactionHistoryTable.PRODUCTID_FLD] = drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD];
                drowHistory[MST_TransactionHistoryTable.CCNID_FLD] = voWOCompletion.CCNID;
                drowHistory[MST_TransactionHistoryTable.LOT_FLD] = voWOCompletion.Lot;
                drowHistory[MST_TransactionHistoryTable.MASTERLOCATIONID_FLD] = voWOCompletion.MasterLocationID;
                drowHistory[MST_TransactionHistoryTable.STOCKUMID_FLD] = dtbListOfComponentShortage.Rows[i][ITM_ProductTable.STOCKUMID_FLD];
                drowHistory[MST_TransactionHistoryTable.SERIAL_FLD] = voWOCompletion.Serial;
                drowHistory[MST_TransactionHistoryTable.POSTDATE_FLD] = voWOCompletion.PostDate;
                drowHistory[MST_TransactionHistoryTable.REFMASTERID_FLD] = pintWOCompletionID;
                drowHistory[MST_TransactionHistoryTable.QUANTITY_FLD] = -decShortgeQuantity;
                drowHistory[MST_TransactionHistoryTable.ISSUEPUROSEID_FLD] = (int)PurposeEnum.Completion;
                dstHistory.Tables[0].Rows.Add(drowHistory);

                #endregion

                #region Cache

                int intLocationID = (int)drowItemsInCache[0][MST_BINTable.LOCATIONID_FLD];
                int intBinID = (int)drowItemsInCache[0][MST_BINTable.BINID_FLD];
                int intProductID = (int)drowItemsInCache[0][ITM_ProductTable.PRODUCTID_FLD];
                string strFilterBin = "LocationID=" + intLocationID + " AND BinID=" + intBinID
                                      + " AND ProductID=" + intProductID;
                string strFilterLoc = "LocationID=" + intLocationID + " AND ProductID=" + intProductID;

                // bin cache
                DataRow[] drBin = dstBin.Tables[0].Select(strFilterBin);
                if (drBin.Length > 0)
                {
                    try
                    {
                        drBin[0][IV_BinCacheTable.OHQUANTITY_FLD] = (decimal)drBin[0][IV_BinCacheTable.OHQUANTITY_FLD]
                                                                    - decShortgeQuantity;
                    }
                    catch { }
                }

                // location cache
                DataRow[] drLocation = dstLoc.Tables[0].Select(strFilterLoc);
                if (drLocation.Length > 0)
                {
                    try
                    {
                        drLocation[0][IV_LocationCacheTable.OHQUANTITY_FLD] = (decimal)drLocation[0][IV_LocationCacheTable.OHQUANTITY_FLD]
                                                                              - decShortgeQuantity;
                    }
                    catch { }
                }

                // master location cache
                DataRow[] drMasLocation = dstMasLoc.Tables[0].Select("ProductID=" + intProductID);
                if (drMasLocation.Length > 0)
                {
                    try
                    {
                        drMasLocation[0][IV_MasLocCacheTable.OHQUANTITY_FLD] = (decimal)drMasLocation[0][IV_MasLocCacheTable.OHQUANTITY_FLD]
                                                                               - decShortgeQuantity;
                    }
                    catch { }
                }

                #endregion
            }

            #region Cache

            if (voWOCompletion.QAStatus == (int)QAStatusEnum.Passed)
            {
                string strFilterBin1 = "LocationID=" + voWOCompletion.LocationID + " AND BinID=" + voWOCompletion.BinID
                    + " AND ProductID=" + voWOCompletion.ProductID;
                string strFilterLoc1 = "LocationID=" + voWOCompletion.LocationID + " AND ProductID=" + voWOCompletion.ProductID;

                // bin cache
                DataRow[] drBin1 = dstBin.Tables[0].Select(strFilterBin1);
                if (drBin1.Length > 0)
                {
                    try
                    {
                        drBin1[0][IV_BinCacheTable.OHQUANTITY_FLD] = (decimal)drBin1[0][IV_BinCacheTable.OHQUANTITY_FLD]
                            + voWOCompletion.CompletedQuantity;
                    }
                    catch { }
                }
                else
                {
                    // add new record
                    DataRow drNew = dstBin.Tables[0].NewRow();
                    drNew[IV_BinCacheTable.CCNID_FLD] = voWOCompletion.CCNID;
                    drNew[IV_BinCacheTable.MASTERLOCATIONID_FLD] = voWOCompletion.MasterLocationID;
                    drNew[IV_BinCacheTable.LOCATIONID_FLD] = voWOCompletion.LocationID;
                    drNew[IV_BinCacheTable.BINID_FLD] = voWOCompletion.BinID;
                    drNew[IV_BinCacheTable.PRODUCTID_FLD] = voWOCompletion.ProductID;
                    drNew[IV_BinCacheTable.OHQUANTITY_FLD] = voWOCompletion.CompletedQuantity;
                    dstBin.Tables[0].Rows.Add(drNew);
                }

                // location cache
                DataRow[] drLocation1 = dstLoc.Tables[0].Select(strFilterLoc1);
                if (drLocation1.Length > 0)
                {
                    try
                    {
                        drLocation1[0][IV_LocationCacheTable.OHQUANTITY_FLD] = (decimal)drLocation1[0][IV_LocationCacheTable.OHQUANTITY_FLD]
                            + voWOCompletion.CompletedQuantity;
                    }
                    catch { }
                }
                else
                {
                    // add new record
                    DataRow drNew = dstLoc.Tables[0].NewRow();
                    drNew[IV_LocationCacheTable.CCNID_FLD] = voWOCompletion.CCNID;
                    drNew[IV_LocationCacheTable.MASTERLOCATIONID_FLD] = voWOCompletion.MasterLocationID;
                    drNew[IV_LocationCacheTable.LOCATIONID_FLD] = voWOCompletion.LocationID;
                    drNew[IV_LocationCacheTable.PRODUCTID_FLD] = voWOCompletion.ProductID;
                    drNew[IV_LocationCacheTable.OHQUANTITY_FLD] = voWOCompletion.CompletedQuantity;
                    dstLoc.Tables[0].Rows.Add(drNew);
                }

                // master location cache
                DataRow[] drMasLocation1 = dstMasLoc.Tables[0].Select("ProductID=" + voWOCompletion.ProductID);
                if (drMasLocation1.Length > 0)
                {
                    try
                    {
                        drMasLocation1[0][IV_MasLocCacheTable.OHQUANTITY_FLD] = (decimal)drMasLocation1[0][IV_MasLocCacheTable.OHQUANTITY_FLD]
                            + voWOCompletion.CompletedQuantity;
                    }
                    catch { }
                }
                else
                {
                    // add new record
                    DataRow drNew = dstMasLoc.Tables[0].NewRow();
                    drNew[IV_MasLocCacheTable.CCNID_FLD] = voWOCompletion.CCNID;
                    drNew[IV_MasLocCacheTable.MASTERLOCATIONID_FLD] = voWOCompletion.MasterLocationID;
                    drNew[IV_MasLocCacheTable.PRODUCTID_FLD] = voWOCompletion.ProductID;
                    drNew[IV_MasLocCacheTable.OHQUANTITY_FLD] = voWOCompletion.CompletedQuantity;
                    dstMasLoc.Tables[0].Rows.Add(drNew);
                }
            }

            #endregion

            if (pdecQuantity <= voWOCompletion.CompletedQuantity)
            {
                PRO_WorkOrderDetailDS dsPRO_WorkOrderDetail = new PRO_WorkOrderDetailDS();
                dsPRO_WorkOrderDetail.UpdateWOCompletion(WOLineStatus.MfgClose, voWOCompletion.WorkOrderDetailID);
            }

            //SaveTransactionHistory
            DataRow drowHist = dstHistory.Tables[0].NewRow();
            drowHist[MST_TransactionHistoryTable.BINID_FLD] = voWOCompletion.BinID;
            drowHist[MST_TransactionHistoryTable.TRANSDATE_FLD] = dtmServerDate;
            drowHist[MST_TransactionHistoryTable.TRANTYPEID_FLD] = 19;//(int)TransactionTypeEnum.PROWorkOrderCompletion;
            drowHist[MST_TransactionHistoryTable.LOCATIONID_FLD] = voWOCompletion.LocationID;
            drowHist[MST_TransactionHistoryTable.PRODUCTID_FLD] = voWOCompletion.ProductID;
            drowHist[MST_TransactionHistoryTable.CCNID_FLD] = voWOCompletion.CCNID;
            drowHist[MST_TransactionHistoryTable.LOT_FLD] = voWOCompletion.Lot;
            drowHist[MST_TransactionHistoryTable.MASTERLOCATIONID_FLD] = voWOCompletion.MasterLocationID;
            drowHist[MST_TransactionHistoryTable.STOCKUMID_FLD] = voWOCompletion.StockUMID;
            drowHist[MST_TransactionHistoryTable.SERIAL_FLD] = voWOCompletion.Serial;
            drowHist[MST_TransactionHistoryTable.POSTDATE_FLD] = voWOCompletion.PostDate;
            drowHist[MST_TransactionHistoryTable.REFMASTERID_FLD] = pintWOCompletionID;
            drowHist[MST_TransactionHistoryTable.REFDETAILID_FLD] = pintWOCompletionID;
            drowHist[MST_TransactionHistoryTable.QUANTITY_FLD] = voWOCompletion.CompletedQuantity;
            drowHist[MST_TransactionHistoryTable.ISSUEPUROSEID_FLD] = (int)PurposeEnum.Completion;
            dstHistory.Tables[0].Rows.Add(drowHist);

            dsHistory.UpdateDataSet(dstHistory);
            dsBin.UpdateDataSet(dstBin);
            dsLoc.UpdateDataSet(dstLoc);
            dsMasLoc.UpdateDataSet(dstMasLoc);

            return pintWOCompletionID;
        }
        public string UpdateWorkOrderCompletion(PRO_WorkOrderCompletion voWOCompletion, DateTime dtmServerDate, DataTable dtBomlist)
        {
            bool ret = true;
            string strProductCode = "";
            PRO_WorkOrderCompletion OldWOCompletionVO = new PRO_WorkOrderCompletion();
            //1. Get WOCompletionInof Old and Update WOCompletion
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    var obj = db.PRO_WorkOrderCompletions.SingleOrDefault<PRO_WorkOrderCompletion>(e => e.WorkOrderCompletionID == voWOCompletion.WorkOrderCompletionID);
                    OldWOCompletionVO = obj;
                    obj.BinID = voWOCompletion.BinID;
                    obj.CCNID = voWOCompletion.CCNID;
                    obj.CompletedDate = voWOCompletion.CompletedDate;
                    obj.CompletedQuantity = voWOCompletion.CompletedQuantity;
                    obj.IssuePurposeID = voWOCompletion.IssuePurposeID;
                    obj.KCSUser = voWOCompletion.KCSUser;
                    obj.LastChange = voWOCompletion.LastChange;
                    obj.LocationID = voWOCompletion.LocationID;
                    obj.Lot = voWOCompletion.Lot;
                    obj.MasterLocationID = voWOCompletion.MasterLocationID;
                    obj.NGQuantity = voWOCompletion.NGQuantity;
                    obj.PostDate = voWOCompletion.PostDate;
                    obj.ProductID = voWOCompletion.ProductID;
                    obj.QAStatus = voWOCompletion.QAStatus;
                    obj.Remark = voWOCompletion.Remark;
                    obj.Serial = voWOCompletion.Serial;
                    obj.ShiftID = voWOCompletion.ShiftID;
                    obj.StockUMID = voWOCompletion.StockUMID;
                    obj.UserName = voWOCompletion.UserName;
                    obj.WOCompletionNo = voWOCompletion.WOCompletionNo;
                    obj.WorkOrderDetailID = voWOCompletion.WorkOrderDetailID;
                    obj.WorkOrderMasterID = voWOCompletion.WorkOrderMasterID;
                    db.SubmitChanges();

                    int intTranTypeID = 19;//(int)TransactionTypeEnum.PROWorkOrderCompletion;
                    DateTime dtmTransDate = dtmServerDate;

                    //3. Update Inventory
                    if (voWOCompletion.QAStatus == (int)QAStatusEnum.Passed)
                    {
                        // The first add NewQuantity after that subtract OldQuantity
                        //3.1 Add Quantity
                        InventoryUtilsBO objInventoryBO = new InventoryUtilsBO();
                        //UpdateAddSubstract
                        objInventoryBO.UpdateAddOHQtyAll(voWOCompletion.CCNID, voWOCompletion.MasterLocationID, voWOCompletion.LocationID, voWOCompletion.ProductID, voWOCompletion.BinID, 0, 0, string.Empty, string.Empty, true, true, true, false, false, db);
                        //AddAndSubtractWOCompletion(voWOCompletion, 1);
                        //3.2 Subtract Quantity
                        objInventoryBO.UpdatedSubstractOHQtyAll(voWOCompletion.CCNID, voWOCompletion.MasterLocationID, voWOCompletion.LocationID, voWOCompletion.ProductID, voWOCompletion.BinID, 0, 0, string.Empty, string.Empty, false, false, true, db);
                        //AddAndSubtractWOCompletion(OldWOCompletionVO, -1);
                    }

                    //4. Update status                    
                    decimal decQuantityCompletion = (from objWO in db.PRO_WorkOrderDetails
                                                     where objWO.WorkOrderDetailID == voWOCompletion.WorkOrderDetailID
                                                     select objWO.OrderQuantity).Sum();
                    byte iStatusWOLine = (byte)WOLineStatus.Released;
                    if (decQuantityCompletion <= voWOCompletion.CompletedQuantity) iStatusWOLine = (byte)WOLineStatus.MfgClose;
                    var objWOLine = db.PRO_WorkOrderDetails.SingleOrDefault(e => e.WorkOrderDetailID == voWOCompletion.WorkOrderDetailID);
                    objWOLine.Status = iStatusWOLine;
                    db.SubmitChanges();                    

                    #region transaction history of parent

                    MST_TransactionHistory voTransactionHistory = new MST_TransactionHistory();
                    if (voWOCompletion.BinID != null)
                    {
                        int iBinID = voWOCompletion.BinID ?? 0;
                        voTransactionHistory.BinID = iBinID;
                    }
                    voTransactionHistory.TransDate = dtmServerDate;
                    voTransactionHistory.TranTypeID = intTranTypeID;
                    voTransactionHistory.LocationID = voWOCompletion.LocationID;
                    voTransactionHistory.ProductID = voWOCompletion.ProductID;

                    voTransactionHistory.CCNID = voWOCompletion.CCNID;
                    voTransactionHistory.Lot = voWOCompletion.Lot;
                    voTransactionHistory.MasterLocationID = voWOCompletion.MasterLocationID;
                    voTransactionHistory.StockUMID = voWOCompletion.StockUMID;
                    voTransactionHistory.Serial = voWOCompletion.Serial;
                    voTransactionHistory.PostDate = voWOCompletion.PostDate;

                    voTransactionHistory.RefMasterID = voWOCompletion.WorkOrderCompletionID;

                    voTransactionHistory.Quantity = voWOCompletion.CompletedQuantity;

                    // Set Comment status
                    switch (voWOCompletion.QAStatus)
                    {
                        case (int)QAStatusEnum.Passed:
                            strComment[0] = QAStatusEnum.Passed.ToString();
                            break;
                        case (int)QAStatusEnum.Failure:
                            strComment[0] = QAStatusEnum.Failure.ToString();
                            break;
                        case (int)QAStatusEnum.Unknow:
                            strComment[0] = QAStatusEnum.Unknow.ToString();
                            break;
                    }
                    //new InventoryUtilsBO().UpdateTransactionHistory(TransactionType.WORK_ORDER_COMPLETION, (int)PurposeEnum.Completion, voTransactionHistory, strComment);
                    new InventoryUtilsBO().SaveTransactionHis(TransactionType.WORK_ORDER_COMPLETION, (int)PurposeEnum.Completion, voTransactionHistory, db, strComment);
                    #endregion
                    
                    //6. Update Inventory by Item by Bom
                    foreach (DataRow rowBom in dtBomlist.Rows)
                    {
                        int intProductTypeID = 0;
                        try
                        {
                            intProductTypeID = int.Parse(rowBom[ITM_ProductTable.PRODUCTTYPEID_FLD].ToString());
                        }
                        catch
                        {
                        }
                        if (intProductTypeID == (int)EnumProductType.SubMaterial) continue;
                        //6.1 Add NewQuantity                        
                        AddAndSubstractWOCompletionBOM(OldWOCompletionVO, -1, rowBom, intTranTypeID, dtmServerDate, db);
                        //6.2 Subtract OldQuantity
                        AddAndSubstractWOCompletionBOM(voWOCompletion, 1, rowBom, intTranTypeID, dtmTransDate, db);
                    }
                }
                trans.Complete();
            }
            return strProductCode;
        }
        public int WorkOrderCompletionForEditBussiness(PRO_WorkOrderCompletion voWOCompletion, DateTime dtmServerDate)
        {

            PRO_WorkOrderCompletion OldWOCompletionVO = new PRO_WorkOrderCompletion();
            //1. Get WOCompletionInof Old
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    OldWOCompletionVO = db.PRO_WorkOrderCompletions.SingleOrDefault<PRO_WorkOrderCompletion>(e => e.WorkOrderCompletionID == voWOCompletion.WorkOrderCompletionID);
                    trans.Complete();
                }
            }
            int intTranTypeID = 19;//(int)TransactionTypeEnum.PROWorkOrderCompletion;
            DateTime dtmTransDate = dtmServerDate;

            //2. Update data to WorkOrderCompletion
            new PRO_WorkOrderCompletionDS().Update(voWOCompletion);

            //3. Update Inventory
            if (voWOCompletion.QAStatus == (int)QAStatusEnum.Passed)
            {
                // The first add NewQuantity after that subtract OldQuantity
                //3.1 Add Quantity
                AddAndSubtractWOCompletion(voWOCompletion, 1);
                //3.2 Subtract Quantity
                AddAndSubtractWOCompletion(OldWOCompletionVO, -1);
            }

            //4. Update status
            decimal decQuantityCompletion = new PRO_WorkOrderDetailDS().GetOrderQuantityByWODetail(voWOCompletion.WorkOrderDetailID);
            if (decQuantityCompletion <= voWOCompletion.CompletedQuantity)
            {
                PRO_WorkOrderDetailDS dsPRO_WorkOrderDetail = new PRO_WorkOrderDetailDS();
                dsPRO_WorkOrderDetail.UpdateWOCompletion(WOLineStatus.MfgClose, voWOCompletion.WorkOrderDetailID);
            }
            else
            {
                PRO_WorkOrderDetailDS dsPRO_WorkOrderDetail = new PRO_WorkOrderDetailDS();
                dsPRO_WorkOrderDetail.UpdateWOCompletion(WOLineStatus.Released, voWOCompletion.WorkOrderDetailID);
            }
            //5. Get BomInfo of Product
            PRO_WOBOMDS objWOBomDS = new PRO_WOBOMDS();
            DataTable dtBomlist = objWOBomDS.ListBomDetailOfProduct(OldWOCompletionVO.ProductID, OldWOCompletionVO.WorkOrderDetailID);

            #region transaction history of parent

            MST_TransactionHistoryVO voTransactionHistory = new MST_TransactionHistoryVO();
            if (voWOCompletion.BinID != null)
            {
                int iBinID = voWOCompletion.BinID ?? 0;
                voTransactionHistory.BinID = iBinID;
            }
            voTransactionHistory.TransDate = dtmServerDate;
            voTransactionHistory.TranTypeID = intTranTypeID;
            voTransactionHistory.LocationID = voWOCompletion.LocationID;
            voTransactionHistory.ProductID = voWOCompletion.ProductID;

            voTransactionHistory.CCNID = voWOCompletion.CCNID;
            voTransactionHistory.Lot = voWOCompletion.Lot;
            voTransactionHistory.MasterLocationID = voWOCompletion.MasterLocationID;
            voTransactionHistory.StockUMID = voWOCompletion.StockUMID;
            voTransactionHistory.Serial = voWOCompletion.Serial;
            voTransactionHistory.PostDate = voWOCompletion.PostDate;

            voTransactionHistory.RefMasterID = voWOCompletion.WorkOrderCompletionID;

            voTransactionHistory.Quantity = voWOCompletion.CompletedQuantity;

            // Set Comment status
            switch (voWOCompletion.QAStatus)
            {
                case (int)QAStatusEnum.Passed:
                    strComment[0] = QAStatusEnum.Passed.ToString();
                    break;
                case (int)QAStatusEnum.Failure:
                    strComment[0] = QAStatusEnum.Failure.ToString();
                    break;
                case (int)QAStatusEnum.Unknow:
                    strComment[0] = QAStatusEnum.Unknow.ToString();
                    break;
            }
            new InventoryUtilsBO().UpdateTransactionHistory(TransactionType.WORK_ORDER_COMPLETION, (int)PurposeEnum.Completion, voTransactionHistory, strComment);

            #endregion

            //6. Update Inventory by Item by Bom
            foreach (DataRow rowBom in dtBomlist.Rows)
            {
                int intProductTypeID = 0;
                try
                {
                    intProductTypeID = int.Parse(rowBom[ITM_ProductTable.PRODUCTTYPEID_FLD].ToString());
                }
                catch
                {
                }
                if (intProductTypeID == (int)EnumProductType.SubMaterial) continue;
                //6.1 Add NewQuantity
                AddAndSubtractWOCompletionByBom(OldWOCompletionVO, -1, rowBom, intTranTypeID, dtmTransDate);
                //6.2 Subtract OldQuantity
                AddAndSubtractWOCompletionByBom(voWOCompletion, 1, rowBom, intTranTypeID, dtmTransDate);
            }

            return voWOCompletion.WorkOrderCompletionID;
        }

        /// <summary>
        /// LoadWOCompletion
        /// </summary>
        /// <param name="pintWOCompletionID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Wednesday, June 22 2005</date>
        public DataRow LoadWOCompletion(int pintWOCompletionID)
        {
            PRO_WorkOrderCompletionDS dsWOCompletion = new PRO_WorkOrderCompletionDS();
            DataRow drowWOCompletion = dsWOCompletion.GetWOCompletion(pintWOCompletionID);
            return drowWOCompletion;
        }

        /// <summary>
        /// LoadItemInfo
        /// </summary>
        /// <param name="pintProductID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Thursday, June 23 2005</date>
        public DataTable LoadItemInfo(int pintProductID)
        {
            try
            {
                ITM_ProductDS dsProduct = new ITM_ProductDS();
                return dsProduct.LoadItemInfo(pintProductID);

            }
            catch (PCSDBException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        /// <summary>
        /// LoadDataForWOLine
        /// </summary>
        /// <param name="pintWODetailID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Tuesday, June 21 2005</date>
        public DataSet LoadDataForWOLine(int pintWODetailID)
        {
            try
            {
                PRO_WorkOrderCompletionDS dsWOCompletion = new PRO_WorkOrderCompletionDS();
                return dsWOCompletion.LoadDataForWOLine(pintWODetailID);

            }
            catch (PCSDBException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public void ListLocationIssueAndOH(int pintWODetailID, decimal pdecCompleteQuantity)
        {
        }

        /// <summary>
        /// GetWorkingTimeByProductionLineAndYearMonth
        /// </summary>
        /// <param name="pintProductionLineID"></param>
        /// <param name="pintYear"></param>
        /// <param name="pintMonth"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Wednesday, July 19 2006</date>
        public DataSet GetWorkingTimeByProductionLineAndYearMonth(int pintProductionLineID, int pintYear, int pintMonth)
        {
            PRO_WorkOrderCompletionDS dsWorkOrderCompletion = new PRO_WorkOrderCompletionDS();
            return dsWorkOrderCompletion.GetWorkingTimeByProductionLineAndYearMonth(pintProductionLineID, pintYear, pintMonth);
        }

        public void AddAndSubtractWOCompletion(PRO_WorkOrderCompletion woCompletionVo, int sign)
        {
            int iBinID = 0;
            if (woCompletionVo.BinID != null) iBinID = woCompletionVo.BinID ?? 0;
            //update subtract
            if (sign == -1)
            {

                new InventoryUtilsBO().UpdateSubtractOHQuantity(woCompletionVo.CCNID,
                                                                woCompletionVo.MasterLocationID,
                                                                woCompletionVo.LocationID,
                                                                iBinID,
                                                                woCompletionVo.ProductID,
                                                                woCompletionVo.CompletedQuantity,
                                                                string.Empty,
                                                                string.Empty);
            }
            else
            {
                new InventoryUtilsBO().UpdateAddOHQuantity(woCompletionVo.CCNID,
                                                           woCompletionVo.MasterLocationID,
                                                           woCompletionVo.LocationID,
                                                           iBinID,
                                                           woCompletionVo.ProductID,
                                                           woCompletionVo.CompletedQuantity,
                                                           string.Empty,
                                                           string.Empty);
            }
        }

        /// <summary>
        /// AddAndSubtractWOCompletionByBom: sign =1: Subtract; sign =-1 Add
        /// </summary>
        /// <param name="woCompletionVo"></param>
        /// <param name="sign"></param>
        /// <param name="rowBom"></param>
        public bool AddAndSubstractWOCompletionBOM(PRO_WorkOrderCompletion woCompletionVo, int sign, DataRow rowBom, int pintTranTypeID, DateTime pdtmTransDate, PCSDataContext db)
        {
            bool ret = true;
            //using (var trans = new TransactionScope())
            //{
            try
            {
                if (db == null) db = new PCSDataContext(Utils.Instance.ConnectionString);
                //subtract inventory and save transaction
                MST_TransactionHistory voTransactionHistory = new MST_TransactionHistory();
                voTransactionHistory.TransDate = pdtmTransDate;
                voTransactionHistory.TranTypeID = pintTranTypeID;
                voTransactionHistory.ProductID = (int) rowBom[ITM_BOMTable.COMPONENTID_FLD];

                voTransactionHistory.CCNID = woCompletionVo.CCNID;
                voTransactionHistory.Lot = woCompletionVo.Lot;
                voTransactionHistory.StockUMID = woCompletionVo.StockUMID;
                voTransactionHistory.Serial = woCompletionVo.Serial;
                voTransactionHistory.PostDate = woCompletionVo.PostDate;

                voTransactionHistory.RefMasterID = woCompletionVo.WorkOrderCompletionID;

                Decimal decRate = Decimal.One;
                try
                {
                    decRate = (decimal) rowBom[ITM_ProductTable.RATE_FLD];
                }
                catch
                {
                }
                decimal decBOMQty = (decimal) rowBom[ITM_BOMTable.QUANTITY_FLD];


                decimal decQuantity = 0;
                string strRevision = rowBom[ITM_ProductTable.REVISION_FLD].ToString();
                if (strRevision == "CTTC")
                {
                    decQuantity = woCompletionVo.CompletedQuantity*decBOMQty;
                    voTransactionHistory.Quantity = -1*(woCompletionVo.CompletedQuantity*decBOMQty);
                }
                else
                {
                    decQuantity = woCompletionVo.CompletedQuantity*decBOMQty*decRate;
                    voTransactionHistory.Quantity = -1*(woCompletionVo.CompletedQuantity*decBOMQty*decRate);
                }

                voTransactionHistory.MasterLocationID = woCompletionVo.MasterLocationID;
                voTransactionHistory.LocationID = (int) rowBom[MST_LocationTable.LOCATIONID_FLD];

                var list = (from objBIN in db.MST_BINs
                            where
                                objBIN.LocationID == voTransactionHistory.LocationID &&
                                objBIN.BinTypeID == (int) BinTypeEnum.IN
                            select objBIN.BinID).ToList();
                if (list.Count > 0)
                    voTransactionHistory.BinID = list[0];

                // Set Comment status
                switch (woCompletionVo.QAStatus)
                {
                    case (int) QAStatusEnum.Passed:
                        strComment[0] = QAStatusEnum.Passed.ToString();
                        break;
                    case (int) QAStatusEnum.Failure:
                        strComment[0] = QAStatusEnum.Failure.ToString();
                        break;
                    case (int) QAStatusEnum.Unknow:
                        strComment[0] = QAStatusEnum.Unknow.ToString();
                        break;
                }

                //update 
                if (sign == -1)
                {
                    int iLocationID = 0;
                    if (voTransactionHistory.LocationID != null) iLocationID = voTransactionHistory.LocationID ?? 0;
                    int iProductID = 0;
                    if (voTransactionHistory.ProductID != null) iProductID = voTransactionHistory.ProductID ?? 0;
                    new InventoryUtilsBO().UpdateAddOHQtyAll(woCompletionVo.CCNID, voTransactionHistory.MasterLocationID,
                                                             iLocationID, iProductID, voTransactionHistory.BinID,
                                                             decQuantity, 0, string.Empty, string.Empty, true, true,
                                                             true, false, false, db);
                }
                else
                {
                    int iLocationID = 0;
                    if (voTransactionHistory.LocationID != null) iLocationID = voTransactionHistory.LocationID ?? 0;
                    int iProductID = 0;
                    if (voTransactionHistory.ProductID != null) iProductID = voTransactionHistory.ProductID ?? 0;
                    new InventoryUtilsBO().UpdatedSubstractOHQtyAll(woCompletionVo.CCNID,
                                                                    voTransactionHistory.MasterLocationID, iLocationID,
                                                                    iProductID, voTransactionHistory.BinID, decQuantity,
                                                                    0, string.Empty, string.Empty, false, false, true,
                                                                    db);
                    new InventoryUtilsBO().SaveTransactionHis(TransactionType.WORK_ORDER_COMPLETION,
                                                              (int) PurposeEnum.Completion, voTransactionHistory, db,
                                                              strComment);
                }
            }
            catch (Exception ex)
            {
                ret = false;
                Transaction.Current.Rollback();
                throw ex;
            }
            return ret;
        }

        public void AddAndSubtractWOCompletionByBom(PRO_WorkOrderCompletion woCompletionVo, int sign, DataRow rowBom, int pintTranTypeID, DateTime pdtmTransDate)
        {
            //subtract inventory and save transaction
            MST_TransactionHistoryVO voTransactionHistory = new MST_TransactionHistoryVO();
            voTransactionHistory.TransDate = pdtmTransDate;
            voTransactionHistory.TranTypeID = pintTranTypeID;
            voTransactionHistory.ProductID = (int)rowBom[ITM_BOMTable.COMPONENTID_FLD];

            voTransactionHistory.CCNID = woCompletionVo.CCNID;
            voTransactionHistory.Lot = woCompletionVo.Lot;
            voTransactionHistory.StockUMID = woCompletionVo.StockUMID;
            voTransactionHistory.Serial = woCompletionVo.Serial;
            voTransactionHistory.PostDate = woCompletionVo.PostDate;

            voTransactionHistory.RefMasterID = woCompletionVo.WorkOrderCompletionID;

            Decimal decRate = Decimal.One;
            try
            {
                decRate = (decimal)rowBom[ITM_ProductTable.RATE_FLD];
            }
            catch
            {
            }
            decimal decBOMQty = (decimal)rowBom[ITM_BOMTable.QUANTITY_FLD];


            decimal decQuantity = 0;
            string strRevision = rowBom[ITM_ProductTable.REVISION_FLD].ToString();
            if (strRevision == "CTTC")
            {
                decQuantity = woCompletionVo.CompletedQuantity * decBOMQty;
                voTransactionHistory.Quantity = -1 * (woCompletionVo.CompletedQuantity * decBOMQty);
            }
            else
            {
                decQuantity = woCompletionVo.CompletedQuantity * decBOMQty * decRate;
                voTransactionHistory.Quantity = -1 * (woCompletionVo.CompletedQuantity * decBOMQty * decRate);
            }

            voTransactionHistory.MasterLocationID = woCompletionVo.MasterLocationID;
            voTransactionHistory.LocationID = (int)rowBom[MST_LocationTable.LOCATIONID_FLD];
            MST_BINDS objBINDS = new MST_BINDS();
            voTransactionHistory.BinID = Convert.ToInt32(objBINDS.GetDefaultLocBin(voTransactionHistory.LocationID)[MST_BINTable.BINID_FLD]);

            // Set Comment status
            switch (woCompletionVo.QAStatus)
            {
                case (int)QAStatusEnum.Passed:
                    strComment[0] = QAStatusEnum.Passed.ToString();
                    break;
                case (int)QAStatusEnum.Failure:
                    strComment[0] = QAStatusEnum.Failure.ToString();
                    break;
                case (int)QAStatusEnum.Unknow:
                    strComment[0] = QAStatusEnum.Unknow.ToString();
                    break;
            }

            //update 
            if (sign == -1)
            {
                new InventoryUtilsBO().UpdateAddOHQuantity(woCompletionVo.CCNID,
                                                           voTransactionHistory.MasterLocationID,
                                                           voTransactionHistory.LocationID,
                                                           voTransactionHistory.BinID,
                                                           voTransactionHistory.ProductID,
                                                           decQuantity,
                                                           string.Empty,
                                                           string.Empty);
            }
            else
            {
                new InventoryUtilsBO().UpdateSubtractOHQuantity(woCompletionVo.CCNID,
                                                                voTransactionHistory.MasterLocationID,
                                                                voTransactionHistory.LocationID,
                                                                voTransactionHistory.BinID,
                                                                voTransactionHistory.ProductID,
                                                                decQuantity,
                                                                string.Empty,
                                                                string.Empty);
                new InventoryUtilsBO().UpdateTransactionHistory(TransactionType.WORK_ORDER_COMPLETION, (int)PurposeEnum.Completion, voTransactionHistory, strComment);
            }
        }

        public void DeleteWOCompletion(int printWOCompletionID)
        {
            int constTranTypeID = 27;
            int constinsStatus = 19;
            int constOldTranTypeID = (new MST_TranTypeDS()).GetTranTypeID(TransactionType.WORK_ORDER_COMPLETION);
            DateTime dtmTransDate = new UtilsBO().GetDBDate();
            //1. Get WOCompletionInof Old
            PRO_WorkOrderCompletion workOrderCompletionVO = new PRO_WorkOrderCompletion();
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    workOrderCompletionVO = db.PRO_WorkOrderCompletions.SingleOrDefault<PRO_WorkOrderCompletion>(e => e.WorkOrderCompletionID == printWOCompletionID);
                    trans.Complete();
                }
            }

            //2. Subtract Quantity
            if (workOrderCompletionVO.QAStatus == (int)QAStatusEnum.Passed)
            {
                AddAndSubtractWOCompletion(workOrderCompletionVO, -1);
            }

            //3. Delete row
            //3.1 WorkOrderCompletion
            new PRO_WorkOrderCompletionDS().Delete(workOrderCompletionVO.WorkOrderCompletionID);

            //4. Update status
            PRO_WorkOrderDetailDS dsPRO_WorkOrderDetail = new PRO_WorkOrderDetailDS();
            dsPRO_WorkOrderDetail.UpdateWOCompletion(WOLineStatus.Released, workOrderCompletionVO.WorkOrderDetailID);

            //5. Get BomInfo of Product
            PRO_WOBOMDS objWOBomDS = new PRO_WOBOMDS();
            DataTable dtBomlist = objWOBomDS.ListBomDetailOfProduct(workOrderCompletionVO.ProductID, workOrderCompletionVO.WorkOrderDetailID);

            //6. Update Inventory by Item by Bom
            foreach (DataRow rowBom in dtBomlist.Rows)
            {
                int intProductTypeID = 0;
                try
                {
                    intProductTypeID = int.Parse(rowBom[ITM_ProductTable.PRODUCTTYPEID_FLD].ToString());
                }
                catch
                {
                }
                if (intProductTypeID == (int)EnumProductType.SubMaterial) continue;
                //6.1 Subtract OldQuantity
                AddAndSubtractWOCompletionByBom(workOrderCompletionVO, -1, rowBom, constOldTranTypeID, dtmTransDate);
            }
            
            new MST_TransactionHistoryDS().UpdateTranType(workOrderCompletionVO.WorkOrderCompletionID, constOldTranTypeID, constTranTypeID, constinsStatus);
        }
        public DataSet SearchForMultiCompletion(int pintSaleOrderDetailID, int pintProductionLineID, int pintWorkCenterID, int pintLocationID)
        {
            PRO_WorkOrderCompletionDS dsCompletion = new PRO_WorkOrderCompletionDS();
            return dsCompletion.SearchForMultiCompletion(pintSaleOrderDetailID, pintProductionLineID, pintWorkCenterID, pintLocationID);
        }
        public DataRow GetBuffBin(int pintLocationID)
        {
            MST_BINDS dsBIN = new MST_BINDS();
            return dsBIN.GetDefaultLocBin(pintLocationID);
        }

        public decimal GetCompletedQuantity(int pintWorkOrderMasterID, int pintWorkOrderDetailID)
        {
            PRO_WorkOrderCompletionDS dsCompletion = new PRO_WorkOrderCompletionDS();
            return dsCompletion.GetCompletedQuantity(pintWorkOrderMasterID, pintWorkOrderDetailID);
        }

        /// <summary>
        ///     Check if all required component for current work order have enough quantity to complete
        /// </summary>
        /// <param name="workOrderDetailId">Work Order Detail ID</param>
        /// <param name="completeQuantity">Complete Quantity</param>
        /// <returns></returns>
        public bool IsEnoughComponent(int workOrderDetailId, decimal completeQuantity, out string componentCode)
        {
            componentCode = string.Empty;
            using (var dataContext = new PCSDataContext(Utils.Instance.ConnectionString))
            {
                var workOrderDetail = dataContext.PRO_WorkOrderDetails.FirstOrDefault(w => w.WorkOrderDetailID == workOrderDetailId);
                var children = workOrderDetail.PRO_WOBOMs.Select(b => new {BomQuantity = b.Quantity.GetValueOrDefault(0), Component = b.ITM_Product});
                foreach (var item in children)
                {
                    int productType = item.Component.ProductTypeID.GetValueOrDefault(-1);
                    // ignore 2 type of product
                    if (productType == (int)EnumProductType.SubMaterial || productType == (int)EnumProductType.SubMaterialProduce)
                    {
                        continue;
                    }

                    var component = item.Component;
                    decimal requiredQuantity = completeQuantity*item.BomQuantity;
                    // if CTTC, multiple with Rate
                    if (!component.Revision.ToUpperInvariant().Equals("CTTC"))
                    {
                        requiredQuantity = requiredQuantity*component.Rate.GetValueOrDefault(1);
                    }

                    // check available quantity
                    var binCaches = dataContext.IV_BinCaches.Join(dataContext.MST_BINs, binCache => binCache.BinID,
                                                              bin => bin.BinID, (binCache, bin) => new {binCache, bin}).
                        Where(@t => @t.bin.BinTypeID == (int) BinTypeEnum.IN
                                    && @t.binCache.ProductID == component.ProductID
                                    && @t.binCache.LocationID == workOrderDetail.PRO_ProductionLine.LocationID.GetValueOrDefault(0))
                                    .Select(@t => @t.binCache);
                    var available = binCaches.Sum(b => b.OHQuantity.GetValueOrDefault(0) - b.CommitQuantity.GetValueOrDefault(0));
                    if (requiredQuantity > available)
                    {
                        componentCode = component.Code;
                        return false;
                    }
                }
            }

            return true;
        }

        private DataTable CreateDataTable()
        {
            var dtbTemplate = new DataTable();
            dtbTemplate.Columns.Add(LineField, typeof(Int32));
            dtbTemplate.Columns.Add(WorkOrderNoField, typeof(String));
            dtbTemplate.Columns.Add(WorkOrderLineField, typeof(String));
            dtbTemplate.Columns.Add(ParentPartNumberField, typeof(String));
            dtbTemplate.Columns.Add(ParentPartNameField, typeof(String));
            dtbTemplate.Columns.Add(ParentPartModelField, typeof(String));
            dtbTemplate.Columns.Add(ParentStockUmField, typeof(String));
            dtbTemplate.Columns.Add(ITM_ProductTable.PRODUCTID_FLD, typeof(Int32));
            dtbTemplate.Columns.Add(PartNumberField, typeof(String));
            dtbTemplate.Columns.Add(PartNameField, typeof(String));
            dtbTemplate.Columns.Add(PartModelField, typeof(String));
            dtbTemplate.Columns.Add(ChildStockUmField, typeof(String));
            dtbTemplate.Columns.Add(NeedQuantityField, typeof(Decimal));
            dtbTemplate.Columns.Add(AvailableQuantityField, typeof(Decimal));
            dtbTemplate.Columns.Add(ShortageQuantityField, typeof(Decimal), NeedQuantityField + "-" + AvailableQuantityField);
            dtbTemplate.DefaultView.RowFilter = ShortageQuantityField + " > 0";
            return dtbTemplate;
        }

        /// <summary>
        ///     Gets list of components don't have enough available quantity to complete the work order
        /// </summary>
        /// <param name="workOrderDetailId">Work Order Detail to be completed</param>
        /// <param name="completeQuantity">Complete quantity</param>
        /// <returns></returns>
        public DataTable GetBomShortageData(int workOrderDetailId, decimal completeQuantity)
        {
            DataTable data = CreateDataTable();
            using (var dataContext = new PCSDataContext(Utils.Instance.ConnectionString))
            {
                var workOrderDetail = dataContext.PRO_WorkOrderDetails.FirstOrDefault(w => w.WorkOrderDetailID == workOrderDetailId);
                if (workOrderDetail == null)
                {
                    throw new NullReferenceException(string.Format("The work order detail with ID {0} does not exist", workOrderDetailId));
                }

                #region set default value for data table

                var product = workOrderDetail.ITM_Product;
                data.Columns[WorkOrderNoField].DefaultValue = workOrderDetail.PRO_WorkOrderMaster.WorkOrderNo;
                data.Columns[ParentPartNumberField].DefaultValue = product.Code;
                data.Columns[ParentPartNameField].DefaultValue = product.Description;
                data.Columns[ParentPartModelField].DefaultValue = product.Revision;
                data.Columns[ParentStockUmField].DefaultValue = product.MST_UnitOfMeasure5.Code;
                data.Columns[WorkOrderLineField].DefaultValue = workOrderDetail.Line;

                #endregion

                var children = workOrderDetail.PRO_WOBOMs;
                int line = 0;
                foreach (var bomItem in children)
                {
                    var component = bomItem.ITM_Product;
                    int productType = component.ProductTypeID.GetValueOrDefault(-1);
                    // ignore 2 type of product
                    if (productType == (int)EnumProductType.SubMaterial || productType == (int)EnumProductType.SubMaterialProduce)
                    {
                        continue;
                    }

                    decimal neededQuantity = completeQuantity * bomItem.Quantity.GetValueOrDefault(0);
                    // if CTTC, multiple with Rate
                    if (!component.Revision.ToUpperInvariant().Equals("CTTC"))
                    {
                        neededQuantity = neededQuantity * component.Rate.GetValueOrDefault(1);
                    }
                    
                    // check available quantity
                    var binCaches = dataContext.IV_BinCaches.Join(dataContext.MST_BINs, binCache => binCache.BinID,
                                                              bin => bin.BinID, (binCache, bin) => new { binCache, bin }).
                        Where(@t => @t.bin.BinTypeID == (int)BinTypeEnum.IN
                                    && @t.binCache.ProductID == component.ProductID
                                    && @t.binCache.LocationID == workOrderDetail.PRO_ProductionLine.LocationID.GetValueOrDefault(0))
                                    .Select(@t => @t.binCache);
                    var availableQuantity = binCaches.Sum(b => b.OHQuantity.GetValueOrDefault(0) - b.CommitQuantity.GetValueOrDefault(0));
                    if (neededQuantity <= availableQuantity)
                    {
                        continue;
                    }

                    // component which does not has enough available quantity to complete
                    var newRow = data.NewRow();
                    newRow[LineField] = ++line;
                    newRow[ITM_ProductTable.PRODUCTID_FLD] = bomItem.ComponentID;
                    newRow[PartNumberField] = component.Code;
                    newRow[PartNameField] = component.Description;
                    newRow[PartModelField] = component.Revision;
                    newRow[ChildStockUmField] = component.MST_UnitOfMeasure5.Code;
                    newRow[NeedQuantityField] = neededQuantity;
                    newRow[AvailableQuantityField] = availableQuantity;
                    data.Rows.Add(newRow);
                }
            }

            return data;
        }
    }
}